1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Public Class frmStaffPaymentRecord1
4
5 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
6 Me.Close()
7 End Sub
8
9 Public Sub GetData()
10 Try
11 con = New SqlConnection(cs)
12 con.Open()
13 cmd = New SqlCommand("select RTRIM(Staff.StaffID) as [Staff ID],RTRIM(StaffName) as [Staff Name],RTRIM(Designation) as [Designation],sum(StaffPayment.salary) as [Basic Salary],sum(Deduction) as [Deduction],sum(netpay) as [Net Pay] from StaffPayment,Staff where Staff.St_ID=StaffPayment.StaffID group by Staff.StaffID,Staffname,designation order by Staffname", con)
14 Dim myDA As SqlDataAdapter = New SqlDataAdapter(cmd)
15 Dim myDataSet As DataSet = New DataSet()
16 myDA.Fill(myDataSet, "StaffPayment")
17 myDA.Fill(myDataSet, "Staff")
18 dgw.DataSource = myDataSet.Tables("StaffPayment").DefaultView
19 dgw.DataSource = myDataSet.Tables("Staff").DefaultView
20 con.Close()
21 Catch ex As Exception
22 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
23 End Try
24 End Sub
25 Private Sub frmLogs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
26 GetData()
27 End Sub
28 Sub Reset()
29 txtStaffName.Text = ""
30 DateFrom.Text = Today
31 DateTo.Text = Now
32 GetData()
33 End Sub
34 Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
35 Reset()
36 End Sub
37
38
39 Private Sub btnClose_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
40 Me.Close()
41 End Sub
42
43 Private Sub btnExportExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportExcel.Click
44 Dim rowsTotal, colsTotal As Short
45 Dim I, j, iC As Short
46 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
47 Dim xlApp As New Excel.Application
48 Try
49 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
50 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
51 xlApp.Visible = True
52
53 rowsTotal = dgw.RowCount
54 colsTotal = dgw.Columns.Count - 1
55 With excelWorksheet
56 .Cells.Select()
57 .Cells.Delete()
58 For iC = 0 To colsTotal
59 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
60 Next
61 For I = 0 To rowsTotal - 1
62 For j = 0 To colsTotal
63 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
64 Next j
65 Next I
66 .Rows("1:1").Font.FontStyle = "Bold"
67 .Rows("1:1").Font.Size = 12
68
69 .Cells.Columns.AutoFit()
70 .Cells.Select()
71 .Cells.EntireColumn.AutoFit()
72 .Cells(1, 1).Select()
73 End With
74 Catch ex As Exception
75 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
76 Finally
77 'RELEASE ALLOACTED RESOURCES
78 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
79 xlApp = Nothing
80 End Try
81 End Sub
82
83 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
84 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
85 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
86 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
87 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
88 End If
89 Dim b As Brush = SystemBrushes.ControlText
90 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
91
92 End Sub
93
94 Private Sub txtStaffname_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtStaffName.TextChanged
95 Try
96 con = New SqlConnection(cs)
97 con.Open()
98 cmd = New SqlCommand("select RTRIM(Staff.StaffID) as [Staff ID],RTRIM(StaffName) as [Staff Name],RTRIM(Designation) as [Designation],sum(StaffPayment.salary) as [Basic Salary],sum(Deduction) as [Deduction],sum(netpay) as [Net Pay] from StaffPayment,Staff where Staff.St_ID=StaffPayment.StaffID and Staffname like '" & txtStaffName.Text & "%' group by Staff.StaffID,Staffname,designation order by Staffname", con)
99 Dim myDA As SqlDataAdapter = New SqlDataAdapter(cmd)
100 Dim myDataSet As DataSet = New DataSet()
101 myDA.Fill(myDataSet, "StaffPayment")
102 myDA.Fill(myDataSet, "Staff")
103 dgw.DataSource = myDataSet.Tables("StaffPayment").DefaultView
104 dgw.DataSource = myDataSet.Tables("Staff").DefaultView
105 con.Close()
106 Catch ex As Exception
107 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
108 End Try
109 End Sub
110
111 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
112 Try
113 con = New SqlConnection(cs)
114 con.Open()
115 cmd = New SqlCommand("select RTRIM(Staff.StaffID) as [Staff ID],RTRIM(StaffName) as [Staff Name],RTRIM(Designation) as [Designation],sum(StaffPayment.salary) as [Basic Salary],sum(Deduction) as [Deduction],sum(netpay) as [Net Pay] from StaffPayment,Staff where Staff.St_ID=StaffPayment.StaffID and PaymentDate between @d1 and @d2 group by Staff.StaffID,Staffname,designation order by Staffname", con)
116 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "DateIN").Value = DateFrom.Value.Date
117 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "DateIN").Value = DateTo.Value
118 Dim myDA As SqlDataAdapter = New SqlDataAdapter(cmd)
119 Dim myDataSet As DataSet = New DataSet()
120 myDA.Fill(myDataSet, "StaffPayment")
121 myDA.Fill(myDataSet, "Staff")
122 dgw.DataSource = myDataSet.Tables("StaffPayment").DefaultView
123 dgw.DataSource = myDataSet.Tables("Staff").DefaultView
124 con.Close()
125 Catch ex As Exception
126 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
127 End Try
128 End Sub
129 End Class
